﻿Imports System.Data.OleDb
Public Class List_Form
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Dim totaldebit As Double
    Dim totalcredit As Double
    Dim qry As String
    Dim dfrom As Date
    Dim dto As Date
    Private Sub List_Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim str As String
        dgridlist.Rows.Clear()
        str = "select customer_id,customer_name from customer_master order by customer_name"
        filldgrid(str)
        dgriddetail.Enabled = False
    End Sub
    Private Sub filldgrid(ByVal str As String)
        Dim i As Integer
        dgridlist.Rows.Clear()
        cmd = New OleDbCommand(str, con)
        dr = cmd.ExecuteReader
        While dr.Read
            dgridlist.Rows.Add()
            dgridlist.Item(0, i).Value = dr(0)
            dgridlist.Item(1, i).Value = dr(1)
            i = i + 1
        End While
    End Sub
    Private Sub btnlist_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlist.Click
        Try
            Dim str As String
            Dim i As Integer
            Dim d As Double
            Dim ob As Object
            Dim camount As Double
            If dtfrom.Value > dtto.Value Then
                MsgBox("'Date from' is larger then 'Date to'")
                Exit Sub
            End If
            If dgridlist.Rows.Count = 0 Then
                MsgBox("No Party exist for list")
                Exit Sub
            End If
            dgriddetail.Rows.Clear()
            str = "select sum(camount)-sum(damount) from ladger where accountid=" & Val(dgridlist.Item(0, dgridlist.CurrentRow.Index).Value) & " and billdate<#" & Format(dtfrom.Value, "dd/MMM/yyyy") & "#"
            cmd = New OleDbCommand(str, con)
            ob = cmd.ExecuteScalar
            If ob.ToString = "" Then
                camount = 0
            Else
                camount = Val(ob.ToString)
                dgriddetail.Rows.Add()
                dgriddetail.Item(0, i).Value = Format(dtfrom.Value, "dd/MMM/yyyy")
                dgriddetail.Item(1, i).Value = "Previous credit balance"
                dgriddetail.Item(2, i).Value = camount
                dgriddetail.Item(4, i).Value = Val(dgriddetail.Item(2, i).Value)
                i = i + 1
            End If
            str = "select billdate,narration,damount,camount from ladger where accountid=" & Val(dgridlist.Item(0, dgridlist.CurrentRow.Index).Value) & " and billdate between #" & Format(dtfrom.Value, "dd/MMM/yyyy") & "# and #" & Format(dtto.Value, "dd/MMM/yyyy") & "# order by billdate"
            cmd = New OleDbCommand(str, con)
            qry = str
            dfrom = dtfrom.Value
            dto = dtto.Value
            dr = cmd.ExecuteReader
            While dr.Read
                dgriddetail.Rows.Add()
                dgriddetail.Item(0, i).Value = Format(dr(0), "dd/MMM/yyyy")
                dgriddetail.Item(1, i).Value = dr(1).ToString
                d = Val(dr(2).ToString)
                totaldebit = totaldebit + d
                dgriddetail.Item(3, i).Value = d.ToString("#0.00")
                d = 0
                d = Val(dr(3).ToString)
                totalcredit = totalcredit + d
                dgriddetail.Item(2, i).Value = d.ToString("#0.00")
                d = 0
                If i = 0 Then
                    dgriddetail.Item(4, i).Value = Val(dgriddetail.Item(2, i).Value) - Val(dgriddetail.Item(3, i).Value)
                Else
                    dgriddetail.Item(4, i).Value = (Val(dgriddetail.Item(4, i - 1).Value) + Val(dgriddetail.Item(2, i).Value)) - Val(dgriddetail.Item(3, i).Value)
                End If
                i = i + 1
            End While
            dgriddetail.ClearSelection()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnconsolidate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnconsolidate.Click
        Dim str As String
        If dgridlist.Rows.Count = 0 Then
            MsgBox("No Party exist for After date")
            Exit Sub
        End If
        str = "delete from consolidate where accountid=" & Val(dgridlist.Item(0, dgridlist.CurrentRow.Index).Value)
        cmd = New OleDbCommand(str, con)
        cmd.ExecuteNonQuery()
        str = "insert into Consolidate(accountid,cdate) values(" & Val(dgridlist.Item(0, dgridlist.CurrentRow.Index).Value) & ",#" & Format(dtfrom.Value, "dd/MMM/yyyy") & "#)"
        cmd = New OleDbCommand(str, con)
        If cmd.ExecuteNonQuery > 0 Then
            MsgBox("After date saved")
        Else
            MsgBox("Error while updation")
            Exit Sub
        End If
    End Sub
    Private Sub dgridlist_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgridlist.CellEnter
        Dim str As String
        Dim dr1 As OleDbDataReader
        If dgridlist.Rows.Count = 0 Then
            Exit Sub
        End If
        If Val(lblid.Text) = Val(dgridlist.Item(0, dgridlist.CurrentRow.Index).Value) Then
        Else
            dgriddetail.Rows.Clear()
        End If
        lblid.Text = Val(dgridlist.Item(0, dgridlist.CurrentRow.Index).Value)
        str = "select max(cdate) from Consolidate where accountid=" & Val(dgridlist.Item(0, dgridlist.CurrentRow.Index).Value)
        cmd = New OleDbCommand(str, con)
        dr1 = cmd.ExecuteReader
        If dr1.Read Then
            If dr1(0).ToString = "" Then
                dtfrom.Value = Today.Date
                dtto.Value = Today.Date
            Else
                dtfrom.Value = dr1(0)
                dtto.Value = dr1(0)
            End If
        End If
    End Sub

    Private Sub btnprint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnprint.Click
        If dgriddetail.Rows.Count = 0 Then
            MsgBox("No Record for Report selected")
            Exit Sub
        End If
        report.fillreport(Val(dgridlist.Item(0, dgridlist.CurrentRow.Index).Value), qry, dfrom, dto, dgridlist.Item(1, dgridlist.CurrentRow.Index).Value)
    End Sub

    Private Sub btnclosing_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Master.Enabled = False
        PartyList.Show()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Master.Enabled = False
        PartyList.Show()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
    End Sub

    Private Sub txtsearch_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtsearch.KeyPress
        If sender.text.contains("'") = True Then
            sender.text = Replace(sender.text, "'", "")
        End If
    End Sub

    Private Sub txtsearch_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtsearch.KeyUp
        Dim str As String
        If sender.text = "" Then
            str = "select customer_id,customer_name from customer_master order by customer_name"
            filldgrid(str)
        Else
            str = "select customer_id,customer_name from customer_master where customer_name LIke '" & txtsearch.Text & "%' order by customer_name"
            filldgrid(str)
        End If
    End Sub
End Class